"""

编程实现：
使用openpyxl在excel中写入10列2000行的数据，
然后使用openpyxl读取出来并使用pymysql写入到数据库中。

"""
import openpyxl
import pymysql
# 写入数据

# file = open('user_info.xlsx', 'w', encoding='utf-8')
"""workbook = openpyxl.Workbook('user_info.xlsx')
#
worksheet = workbook.create_sheet('user_info')

data = [
    ['id', 'idcard', 'username', 'realname', 'pwd', 'telphone', 'age', 'sex'],
    [1, '111', '一一', '赵一', '111111', '15517351361', 11, '男'],
    [2, '222', '二二', '孙二', '222222', '15517351362', 12, '男'],
    [3, '333', '三三', '李三', '333333', '15517351363', 13, '男'],
    [4, '444', '四四', '钱四', '444444', '15517351364', 14, '女']
]
for i in data:
    worksheet.append(i)

workbook.save('user_info.xlsx')"""

# 连接数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    database='user_info',
    user='root',
    password='123456',
    charset='utf8'

)
cursor = db.cursor()

# 读取数据
workbook = openpyxl.load_workbook('user_info.xlsx')
worksheet = workbook['user_info']

for i in worksheet.iter_rows(min_row=2,max_row=5,min_col=1,max_col=8,values_only=True):
    for j in i:
        print(j,end='\t')
    print()
    sql = """insert into user_info (id,idcard,username,realname,pwd,telphone,age,sex) values (%s,%s,%s,%s,%s,%s,%s,%s)"""

    cursor.execute(sql,i)
    db.commit()

cursor.close()
db.close()

# # 连接数据库
# db = pymysql.connect(
#     host='localhost',
#     port=3306,
#     database='user_info',
#     user='root',
#     password='123456',
#     charset='utf8'
# )
# cursor = db.cursor()
#
# # 确保数据表存在（如果不存在则创建）
# create_table_sql = """
# CREATE TABLE IF NOT EXISTS user_info (
#     id INT PRIMARY KEY,
#     idcard VARCHAR(20),
#     username VARCHAR(50),
#     realname VARCHAR(50),
#     pwd VARCHAR(50),
#     telphone VARCHAR(20),
#     age INT,
#     sex VARCHAR(10)
# )
# """
# cursor.execute(create_table_sql)
#
# # 读取Excel数据
# workbook = openpyxl.load_workbook('user_info.xlsx')
# worksheet = workbook['user_info']
#
# # 读取并插入数据（从第2行到第5行，共4条数据）
# for row in worksheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=8, values_only=True):
#     # 打印当前行数据
#     for value in row:
#         print(value, end='\t')
#     print()
#
#     # 构造插入SQL语句
#     sql = """
#     INSERT INTO user_info (id, idcard, username, realname, pwd, telphone, age, sex)
#     VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
#     """
#
#     # 执行SQL并提交
#     cursor.execute(sql, row)
#     db.commit()
#
# # 关闭连接
# cursor.close()
# db.close()
# print("数据已成功写入数据库")






